import sqlite3
list_info = [
   {"id": "15","showH5" : 0, "addr":"110" },
   {"id": "16","showH5" : 0, "addr":"110" },
   {"id": "17","showH5" : 0, "adr":"110" },
]


list_info_dic = {}

def hasId(id):
    return list_info_dic.get(id)

def add(item):
    id = item.get("id")
    if hasId(id):
        a = 0
    else:
        insert_item_into_db(item)
        list_info_dic[id] = item
        list_info.append(item)

def delete(id):
    if hasId(id):
        item = list_info_dic.get(id)
        delete_item_from_db(item)
        list_info_dic.pop(id)

def edit(item):
    id = item.get("id")
    if hasId(id):
        delete(id)
        add(item)

def getall():
    create_db()
    query_all_from_db()
    return list_info_dic.values()

def getItem(id):
    if hasId(id):
       return  list_info_dic[id]


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def create_db():
    conn=sqlite3.connect('/switch_info_db')
    cursor=conn.cursor()
    try:
        sql='''create table h5info_tb( id text, h5switch text, addr text, zhifu text, aspectRatio text)'''
        cursor.execute(sql)
    except:
        return False
    conn.commit()
    cursor.close()

def insert_item_into_db(item):
    conn=sqlite3.connect('/switch_info_db')
    cursor=conn.cursor()
    sql='''insert into h5info_tb(id, h5switch, addr, zhifu, aspectRatio) values(:st_id, :st_h5switch, :st_addr, :st_zhifu, :st_aspectRatio)'''
    cursor.execute(sql, {'st_id':item['id'], 'st_h5switch':item['h5switch'], 'st_addr':item['addr'], 'st_zhifu':item['zhifu'], 'st_aspectRatio':item['aspectRatio']})
    conn.commit()
    cursor.close()
def query_all_from_db():
    conn=sqlite3.connect('/switch_info_db')
    conn.row_factory=dict_factory
    cursor=conn.cursor()
    sql='''select * from h5info_tb'''
    cursor.execute(sql)
    conn.commit()
    results=cursor.fetchall()
    for result in results:
        add(result)        
    cursor.close()


def delete_item_from_db(item):
    conn=sqlite3.connect('/switch_info_db')
    cursor=conn.cursor()
    cursor.execute("delete from h5info_tb where id = '%s'" %item['id'])
    conn.commit()
    cursor.close()

def delete_all_from_db():
    conn=sqlite3.connect('/switch_info_db')
    cursor=conn.cursor()
    cursor.execute("delete from h5info_tb")
    conn.commit()
    cursor.close()
    
